* 3: Budget outcomes
* Table A10, A11
* Numbers on Page 3, Footnote 19
gl data ".../Replication Files/data"



* Create Dataset with Income and Expenditure Outcomes & GP Population
{

* 1 - Import Data, Create Income and Expenditure Variables
{
* Import + Basic Clean
{
set more off
import excel "$data/I&E 2016-2017.xlsx", firstrow clear
destring AmountRs TotalAmountRs TotalSum L M N, replace
save "$data/temp_budgets.dta", replace

import excel "$data/I&E 2015-2016.xlsx", firstrow clear
append using "$data/temp_budgets.dta"
save "$data/temp_budgets.dta", replace

import excel "$data/I&E 2014-2015.xlsx", firstrow clear
append using "$data/temp_budgets.dta"
save "$data/temp_budgets.dta", replace

import excel "$data/I&E 2013-2014.xlsx", firstrow clear
gen byte notnumeric = regexm(TotalSum, "^[-+]?[0-9]*\.?[0-9]+$")==0
drop if notnumeric==1
destring TotalSum, replace
append using "$data/temp_budgets.dta"
drop Link R
replace Date="201314" if Date=="01-04-2013 To 31-03-2014"
replace Date="201415" if Date=="01-04-2014 To 31-03-2015"
replace Date="201516" if Date=="01-04-2015 To 31-03-2016"
replace Date="201617" if Date=="Income Expenditure (Accountwise) 01-04-2016 To 31-03-2017"
destring Date, replace

duplicates drop District Taluka Village Date Expenditure DetailExpenditure AmountRs TotalAmountRs TotalSum Income DetailIncome L M N ExcessofExpenditureoverIncom ExcessofIncomeoverExpenditur, force
foreach x in District Taluka Village {
replace `x' = upper(subinstr(`x'," ","",.))
replace `x' = subinstr(`x',"(DP)","",.)
replace `x' = subinstr(`x',"(DISTRICT)","",.)
replace `x' = subinstr(`x',"(TP)","",.)
replace `x' = subinstr(`x',"(TALUKA)","",.)
replace `x' = subinstr(`x',"(TALUKO)","",.)
replace `x' = subinstr(`x',"(તાલુકો)","",.)
replace `x' = subinstr(`x',"(TALUKA","",.)
replace `x' = subinstr(`x',"(તાલુકા)","",.)
replace `x' = subinstr(`x',"(જિલ્લો)","",.)
replace `x' = subinstr(`x',"(ગામ)","",.)
replace `x' = subinstr(`x',"(GAM)","",.)
replace `x' = subinstr(`x',"(VILLAGE)","",.)
replace `x' = subinstr(`x',")","",.)
replace `x' = subinstr(`x',"(","",.)
replace `x' = subinstr(`x',"-","",.)
}
drop notnumeric
egen budgetid = group(District Taluka Village)
distinct budgetid
egen budgets = group(budgetid Date)
distinct budgets
}
save "$data/temp_budgets.dta", replace



* Total Inc + Exp, Components
{
* Income
{
use "$data/temp_budgets.dta", clear
keep District Taluka Village Date Income M budgetid
replace Income="ownfundinc" if strpos(Inc,"11,")!=0
replace Income="grantinc" if strpos(Inc,"Grants,")!=0
replace Income="miscinc" if strpos(Inc,"Sale")!=0

ren M amt_
drop if Income==""
duplicates drop budgetid Date Income amt_, force
collapse (sum) amt_, by(District Taluka Village Date Income budgetid)
reshape wide amt_, i(budgetid Date) j(Income) string
foreach var in ownfundinc grantinc miscinc {
ren amt_`var' `var'
replace `var'=0 if `var'==.
}
}
save "$data/budgets.dta", replace



* Income Components
{
use "$data/temp_budgets.dta", clear
keep District Taluka Village Date DetailIncome L budgetid

replace DetailIncome = upper(subinstr(DetailIncome," ","",.))
* Top 5 Categories: collapse (sum) L, by(DetailIncome) 	\\\ 	gsort -L	\\\ 	br
gen top_DetailIncome="inc_INCOME" if DetailIncome=="INCOME"
foreach cat in VAIDYANIK		2515	14THFINANCECOMMISSION	4515  {
replace top_DetailIncome="inc_`cat'" if strpos(DetailIncome,"`cat'")!=0
}

ren L amt_
drop if top_DetailIncome==""
duplicates drop budgetid Date top_DetailIncome amt_, force
collapse (sum) amt_, by(District Taluka Village Date top_DetailIncome budgetid)
ren top_DetailIncome DetailIncome
reshape wide amt_, i(budgetid Date) j(DetailIncome) string
foreach var in INCOME VAIDYANIK		2515	14THFINANCECOMMISSION	4515 {
ren amt_inc_`var' inc_detail_`var'
replace inc_detail_`var'=0 if inc_detail_`var'==.
}
merge 1:1 Date budgetid using "$data/budgets.dta", gen(merge)
order Date budgetid District Taluka Village grantinc ownfundinc miscinc
drop merge
}
save "$data/budgets.dta", replace
*



* Expenditure
{
use "$data/temp_budgets.dta", clear
keep District Taluka Village Date Expenditure TotalAmountRs budgetid
replace Expend="ownfundexp" if strpos(Exp,"21,")!=0
replace Expend="grantexp" if strpos(Exp,"22,")!=0
replace Expend="miscexp" if strpos(Exp,"Misc")!=0

ren Total amt_
drop if Expend==""
duplicates drop budgetid Date Expend amt_, force
collapse (sum) amt_, by(District Taluka Village Date Expenditure budgetid)
reshape wide amt_, i(budgetid Date) j(Expend) string
foreach var in ownfundexp grantexp miscexp {
ren amt_`var' `var'
replace `var'=0 if `var'==.
}
merge 1:1 Date budgetid using "$data/budgets.dta", gen(merge)
order Date budgetid District Taluka Village grantexp ownfundexp miscexp grantinc ownfundinc miscinc
drop merge
}
save "$data/budgets.dta", replace



* Expenditure Components
{
use "$data/temp_budgets.dta", clear
keep District Taluka Village Date DetailExpenditure AmountRs budgetid

replace DetailExpenditure = upper(subinstr(DetailExpenditure," ","",.))
* Top 5 Categories: collapse (sum) AmountRs, by(DetailExpen) \\\	gsort -A	\\\ 	br
gen top_DetailExp=""
foreach cat in OTHERADMIN	2059	2515	3054	14THFINANCECOMMISSION {
replace top_DetailExp="exp_`cat'" if strpos(DetailExp,"`cat'")!=0
}

ren AmountRs amt_
drop if top_DetailExp==""
duplicates drop budgetid Date top_DetailExp amt_, force
collapse (sum) amt_, by(District Taluka Village Date top_DetailExp budgetid)
ren top_DetailExp DetailExp
reshape wide amt_, i(budgetid Date) j(DetailExp) string
foreach var in OTHERADMIN	2059	2515	3054	14THFINANCECOMMISSION {
ren amt_exp_`var' exp_detail_`var'
replace exp_detail_`var'=0 if exp_detail_`var'==.
}
merge 1:1 Date budgetid using "$data/budgets.dta", gen(merge)
order Date budgetid District Taluka Village grantexp ownfundexp miscexp exp_detail_OTHERADMIN exp_detail_2059 exp_detail_2515 exp_detail_3054 exp_detail_14THFINANCECOMMISSION grantinc ownfundinc miscinc inc_detail_VAIDYANIK inc_detail_2515 inc_detail_14THFINANCECOMMISSION inc_detail_INCOME inc_detail_4515 
drop merge
}
*


}
*



foreach out in grantexp ownfundexp miscexp exp_detail_OTHERADMIN exp_detail_2059 exp_detail_2515 exp_detail_3054 exp_detail_14THFINANCECOMMISSION grantinc ownfundinc miscinc inc_detail_VAIDYANIK inc_detail_2515 inc_detail_14THFINANCECOMMISSION inc_detail_INCOME inc_detail_4515 {
replace `out'=0 if `out'==.
}


gen income = grantinc + ownfundinc + miscinc
gen expenditure = grantexp + ownfundexp + miscexp
order Date budgetid District Taluka Village income expenditure
}
save "$data/budgets.dta", replace



* 2 - Add in English District + Block Names; Update District Name Changes
{

import excel "$data/Budget_EnglishNames.xlsx", firstrow clear

* Add in English District + Block Names
{
ren DistrictBudget District 
ren TalukaBudget Taluka
keep District Taluka DistrictName BlockName
merge 1:m District Taluka using "$data/budgets.dta", gen(merge)
replace District=DistrictName if DistrictName!=""
replace Taluka=BlockName if BlockName!=""
drop merge BlockName DistrictName
}
order budgetid District Taluka Village Date income expenditure


* District Name Changes
{
* Aravalli was split from Sabarkantha.
tab Taluka if District=="ARAVALLI"
replace District="ARAVALLI" if Taluka=="BAYAD" | Taluka=="BHILODA" | Taluka=="DHANSURA" | Taluka=="MALPUR" | Taluka=="MEGHRAJ" | Taluka=="MODASA"

* Botad was created from parts of Ahmedabad and Bhavanagar districts.
tab Taluka if District=="BOTAD"
replace District="BOTAD" if Taluka=="BARVALA" | Taluka=="BOTAD" | Taluka=="GADHDA" | Taluka=="RANPUR"

*Chhota Udaipur was split from Vadodara District.
tab Taluka if District=="CHHOTAUDEPUR"
replace District="CHHOTAUDEPUR" if Taluka=="BODELI" | Taluka=="CHHOTAUDEPUR" | Taluka=="JETPUR PAVI" | Taluka=="KAWANT" | Taluka=="NASWADI" | Taluka=="SANKHEDA"

*Devbhoomi Dwarka was split from Jamnagar.
tab Taluka if District=="DEVBHUMIDWARKA"
replace District="DEVBHUMIDWARKA" if Taluka=="BHANVAD" | Taluka=="KALYANPUR" | Taluka=="KHAMBHALIA" | Taluka=="OKHAMANDAL"

*Mahisagar was created from parts of Kheda and Panchmahal.
tab Taluka if District=="MAHISAGAR"
replace District="MAHISAGAR" if Taluka=="BALASINOR" | Taluka=="KADANA" | Taluka=="KHANPUR" | Taluka=="LUNAVADA" | Taluka=="SANTRAMPUR" | Taluka=="VIRPUR"

*Morbi was created from parts of Rajkot, Surendranagar and Jamnagar districts.
tab Taluka if District=="MORBI"
replace District="MORBI" if Taluka=="HALWAD" | Taluka=="MALIYAMIYANA" | Taluka=="MORBI" | Taluka=="TANKARA" | Taluka=="WANKANER"

*Gir Somnath was split from Junagadh.
tab Taluka if District=="GIRSOMNATH"
replace District="GIRSOMNATH" if Taluka=="GIRGADHDA" | Taluka=="KODINAR" | Taluka=="PATANVERAVAL" | Taluka=="SUTRAPADA" | Taluka=="TALALA" | Taluka=="UNA"
}
*
}
save "$data/budgets.dta", replace



* 3 - Merge Gujarati GP names with LGD Codes
{
use "$data/budgets.dta", clear
ren Village GPName
duplicates drop District Taluka GPName, force
keep District Taluka GPName budgetid

replace GPName = subinstr(GPName," ","",.)
replace GPName = subinstr(GPName,")","",.)
replace GPName = subinstr(GPName,"(","",.)
replace GPName = subinstr(GPName,",","",.)
replace GPName = subinstr(GPName,".","",.)
replace GPName = subinstr(GPName,";","",.)
replace GPName = subinstr(GPName,":","",.)
replace GPName = subinstr(GPName,"/","",.)

save "$data/budgets_m.dta", replace


* Reclink District - Block - Gujarati GP Names
use "$data/GP_EnglishNames.dta", clear
duplicates tag lgd_id, gen(tag)
drop if tag>0
drop tag
drop District Taluka 
ren DistrictName District 
ren BlockName Taluka

replace GPName = subinstr(GPName," ","",.)
replace GPName = subinstr(GPName,")","",.)
replace GPName = subinstr(GPName,"(","",.)
replace GPName = subinstr(GPName,",","",.)
replace GPName = subinstr(GPName,".","",.)
replace GPName = subinstr(GPName,";","",.)
replace GPName = subinstr(GPName,":","",.)
replace GPName = subinstr(GPName,"/","",.)

reclink District Taluka GPName using "$data/budgets_m.dta", gen(merge) idm(lgd_id) idu(budgetid)
save "$data/temp1_budget.dta", replace


* Reclink District - Block - English GP Names
use "$data/GP_EnglishNames.dta", clear
duplicates tag lgd_id, gen(tag)
drop if tag>0
drop tag
drop District Taluka 
ren DistrictName District 
ren BlockName Taluka

replace PanchayatName = subinstr(PanchayatName," ","",.)
replace PanchayatName = subinstr(PanchayatName,")","",.)
replace PanchayatName = subinstr(PanchayatName,"(","",.)
replace PanchayatName = subinstr(PanchayatName,",","",.)
replace PanchayatName = subinstr(PanchayatName,".","",.)
replace PanchayatName = subinstr(PanchayatName,";","",.)
replace PanchayatName = subinstr(PanchayatName,":","",.)
replace PanchayatName = subinstr(PanchayatName,"/","",.)
ren GPName GPName_Gujarati
ren PanchayatName GPName
reclink District Taluka GPName using "$data/budgets_m.dta", gen(merge) idm(lgd_id) idu(budgetid)
save "$data/temp2_budget.dta", replace



* Keep best matches
use "$data/temp2_budget.dta", clear
append using "$data/temp1_budget.dta"
drop if _merge!=3
drop _merge
drop if GPName==""

bysort lgd_id: egen max_score = max(merge)
drop if merge!=max_score
drop max_score

bysort budgetid: egen max_score = max(merge)
drop if merge!=max_score
drop max_score

* Manual clean for duplicates that survive, by looking through GPName UGPName GPName_Gujarati
{
duplicates tag lgd_id, gen(tag)
order lgd_id budgetid District UDistrict Taluka UTaluka GPName UGPName GPName_Gujarati
sort lgd_id budgetid
br lgd_id budgetid District UDistrict Taluka UTaluka GPName UGPName GPName_Gujarati if tag>0

drop if lgd_id==518 & (budgetid==567 | budgetid==589)
drop if lgd_id==600 & budgetid==667
drop if lgd_id==857 & budgetid==922
drop if lgd_id==918 & budgetid==992
drop if lgd_id==1100 & (budgetid==1206 | budgetid==1207)

drop if lgd_id==1498 & (budgetid==1596 | budgetid==12963)
drop if lgd_id==1621 & tag>0
drop if lgd_id==1955 & (budgetid==13441 | budgetid==13443)
drop if lgd_id==2573 & budgetid==2114
drop if lgd_id==3785 & tag>0

drop if lgd_id==3793 & budgetid==3197
drop if lgd_id==3825 & budgetid==3259
drop if lgd_id==4318 & budgetid==3607
drop if lgd_id==4319 & budgetid==3606
drop if lgd_id==4464 & tag>0

drop if lgd_id==4518 & budgetid==3835
drop if lgd_id==4758 & budgetid==4085
drop if lgd_id==4759 & budgetid==4084
drop if lgd_id==4816 & budgetid==3334
drop if lgd_id==4817 & budgetid==3333

drop if lgd_id==4843 & budgetid==3358
drop if lgd_id==5285 & budgetid==15528
drop if lgd_id==5369 & budgetid==15568
drop if lgd_id==5770 & budgetid==16001 | budgetid==16041
drop if lgd_id==5931 & budgetid==4858

drop if lgd_id==6396 & budgetid==4292
drop if lgd_id==6749 & budgetid==4547
drop if lgd_id==6948 & budgetid==4668
drop if lgd_id==7298 & budgetid==5133
drop if lgd_id==7329 & tag>0

drop if lgd_id==7490 & budgetid==5337
drop if lgd_id==7902 & budgetid==5757
drop if lgd_id==7903 & budgetid==5756
drop if lgd_id==8512 & budgetid==6619
drop if lgd_id==8854 & budgetid==7936

drop if lgd_id==8902 & tag>0
drop if lgd_id==9000 & budgetid==8086
drop if lgd_id==9367 & budgetid==8287
drop if lgd_id==9460 & budgetid==8359
drop if lgd_id==9704 & budgetid==7241

drop if lgd_id==9873 & tag>0
drop if lgd_id==9886 & budgetid==7553
drop if lgd_id==10698 & budgetid==9342
drop if lgd_id==11019 & tag>0
drop if lgd_id==11540 & budgetid==9467

drop if lgd_id==11599 & budgetid==12518
drop if lgd_id==11778 & budgetid==9703
drop if lgd_id==11963 & tag>0
drop if lgd_id==12027 & tag>0
drop if lgd_id==12078 & budgetid==9821

drop if lgd_id==12173 & budgetid==9878
drop if lgd_id==12309 & budgetid==9929
drop if lgd_id==12388 & budgetid==10031

drop if lgd_id==12716 & budgetid==10338
drop if lgd_id==12925 & budgetid==10619
drop if lgd_id==14126 & tag>0
drop if lgd_id==14364 & budgetid==12598

drop if lgd_id==14437 & budgetid==12677
drop if lgd_id==14862 & budgetid==13341
drop if lgd_id==15091 & budgetid==13670
drop if lgd_id==15453 & budgetid==13933
drop if lgd_id==15966 & budgetid==13768

drop if lgd_id==16072 & tag>0
drop if lgd_id==16078 & (budgetid==14394 | budgetid==14369)
drop if lgd_id==16084 & budgetid==14346
drop if lgd_id==16100 & tag>0

drop if lgd_id==16689 & budgetid==15310
drop if lgd_id==16734 & budgetid==15314
drop if lgd_id==16889 & budgetid==15150
drop if lgd_id==17362 & tag>0
drop if lgd_id==136394 & tag>0

drop tag

* check: duplicates report lgd_id

duplicates tag budgetid, gen(tag)
order budgetid lgd_id District UDistrict Taluka UTaluka GPName UGPName GPName_Gujarati
sort budgetid lgd_id
br budgetid lgd_id District UDistrict Taluka UTaluka GPName UGPName GPName_Gujarati if tag>0
 
drop if budgetid==1303 & lgd_id==1185
drop if budgetid==1633 & lgd_id==1582 
drop if budgetid==1651 & tag>0
drop if budgetid==1669 & lgd_id==1648
drop if budgetid==1675 & lgd_id==1659 
drop if budgetid==1687 & lgd_id==1686 

drop if budgetid==1706 & lgd_id==1730 
drop if budgetid==1763 & lgd_id==1860 
drop if budgetid==1789 & lgd_id==1933 
drop if budgetid==1797 & tag>0
drop if budgetid==1812  & lgd_id==1996
drop if budgetid==1878 & lgd_id==2124 

drop if budgetid==1938 & lgd_id==2239
drop if budgetid==2575 & lgd_id==3204 
drop if budgetid==4201 & lgd_id==4891
drop if budgetid==4348 & lgd_id==6501
drop if budgetid==7371 & lgd_id==9931

drop if budgetid==7878 & tag>0
drop if budgetid==8282 & lgd_id==9357
drop if budgetid==8754 & lgd_id==10130 
drop if budgetid==9599 & lgd_id==11678 
drop if budgetid==9606 & lgd_id==11687 

drop if budgetid==9804 & lgd_id==11989
drop if budgetid==9882 & lgd_id==12187
drop if budgetid==9935 & lgd_id==12294
drop if budgetid==10826 & lgd_id==10938
drop if budgetid==13002 & (lgd_id==1578 | lgd_id==1580)

drop if budgetid==13365 & lgd_id==14900 
drop if budgetid==13371 & lgd_id==14907
drop if budgetid==13375 & lgd_id==14909 
drop if budgetid==13429 & lgd_id==1928 
drop if budgetid==13705 & lgd_id==15154 
drop if budgetid==14333 & lgd_id==16054
drop if budgetid==14394 & (lgd_id==16027 | lgd_id==16011)
}
drop tag 

duplicates tag lgd_id, gen(tag)
replace budgetid=. if tag>0
replace merge=0 if tag>0
duplicates drop lgd_id budgetid, force

distinct lgd_id budgetid // no-duplicate check
}
*


}
save "$data/temp3_budget.dta", replace



* Table A.10, A.11; Numbers on Page 3, Footnote 19
{
use "$data/vill_lgd_census11_01.dta", clear
merge 1:1 lgd_id using "$data/temp3_budget.dta", gen(merge0) 

drop if budgetid==.

merge 1:m budgetid using "$data/budgets.dta", gen(merge00)
gen year1 = Date==201314
gen year2 = Date==201415
gen year3 = Date==201516
gen year4 = Date==201617



* Page 3
qui sum income if gp_pop!=.
di round(200000/r(mean),.001)*100
di round(300000/r(mean),.001)*100



* Footnote 19
sum income if inrange(gp_pop,4000,6000)



* Table A.10 Income and Expenditure
{
* Panel 1
foreach out in income ownfundinc expenditure ownfundexp {
local b = 5001
di "`b' `out'"

qui sum `out', d
qui replace `out'=. if `out'>r(p99)
replace `out' = `out'/100000

qui rdrobust `out' gp_pop if inrange(gp_pop,`b'-1000,`b'+999), c(`b') all bwselect(msetwo) vce(cluster gp_pop) covs(year1 year2 year3 gp_scst prop_scst)
di "b" round(e(tau_bc),.001) "  p-val: " round(e(pv_rb),.001) "  ci:["round(e(ci_l_rb),.01) " "  round(e(ci_r_rb),.01) "]   "

qui sum `out' if inrange(gp_pop,`b'-1000,`b'+999)
di "mean: " round(r(mean),.001)

di "obs:" r(N)
di ""
}
*



* Panel 2: Cutoffs = 4001, 6001
* 4001 estimate scaled by 9/11; 6001 estimate scaled by 13/11
{
gen c = 4001 
replace c = 6001 if gp_pop>=5001

gen bwselect = "msetwo" in 1
replace bwselect = "msetwo" in 2

gen vce = "cluster gp_pop" in 1
replace vce = "cluster gp_pop" in 2

gen covs = "year1 year2 year3 gp_scst prop_scst" in 1
replace covs = "year1 year2 year3 gp_scst prop_scst" in 2

foreach out in income ownfundinc expenditure ownfundexp {
di "`out'" 

qui rdmc `out' gp_pop if inrange(gp_pop,3001,7000), c(c) bwselect(bwselect) vce(vce) covs(covs)
lincom (c1*9/11 + c2*13/11)/2

qui sum `out' if inrange(gp_pop,3001,7000)
di "mean: " round(r(mean),.001)

di "obs:" r(N)
di ""
}
}
*



* Panel 3: Cutoff = 8001
* Estimate scaled by 17/11
local b = 8001
foreach out in income ownfundinc expenditure ownfundexp {
di "`out'"

qui rdrobust `out' gp_pop if inrange(gp_pop,`b'-1000,`b'+999), c(`b') all bwselect(msetwo) vce(cluster gp_pop) covs(year1 year2 year3 gp_scst prop_scst)
di "b" round(e(tau_bc)*17/11,.001) "  p-val: " round(e(pv_rb),.001) "  ci:["round(e(ci_l_rb)*17/11,.01) " "  round(e(ci_r_rb)*17/11,.01) "]   "

qui sum `out' if inrange(gp_pop,`b'-1000,`b'+999)
di "mean: " round(r(mean),.001)

di "obs:" r(N)
di ""
}
*


}
*



* A.11 Income & Expenditure Sub-Categories
{
local b = 5001

foreach out in inc_detail_VAIDYANIK inc_detail_2515 inc_detail_INCOME inc_detail_4515 inc_detail_14THFINANCECOMMISSION exp_detail_OTHERADMIN exp_detail_2059 exp_detail_2515 exp_detail_3054 exp_detail_14THFINANCECOMMISSION {

di "`out'"
qui sum `out', d
qui replace `out'=. if `out'>r(p99)
replace `out' = `out'/100000

qui rdrobust `out' gp_pop if inrange(gp_pop,`b'-1000,`b'+999), c(`b') all bwselect(msetwo) vce(cluster gp_pop) covs(year1 year2 year3 gp_scst prop_scst)
di "b" round(e(tau_bc),.001) "  p-val: " round(e(pv_rb),.001) "  ci:["round(e(ci_l_rb),.01) " "  round(e(ci_r_rb),.01) "]   "

qui sum `out' if inrange(gp_pop,`b'-1000,`b'+999)
di "mean: " round(r(mean),.001)

di "obs:" r(N)
di ""
*
}
*
}
*



}
*
